<?php

/**
 * DAO for {@link Content}.
 * <p>
 * It is also a service, ideally, this class should be divided into DAO and Service.
 */
final class ContentDao {

    /** @var PDO */
    private $db = null;

    public function __destruct() {
        // close db connection
        $this->db = null;
    }

    /**
     * Find all {@link Content}s by search criteria.
     * @return array array of {@link Content}s
     */
    public function find(ContentSearchCriteria $search = null) {
        $result = array();
        foreach ($this->query($this->getFindSql($search)) as $row) {
            $content = new Content();
            ContentMapper::map($content, $row);
            $result[$content->getId()] = $content;
        }
        return $result;
    }
    
    /**
     * Find all {@link Content}s by search criteria.
     * @return array array of {@link Content}s
     */
    public function findByCategoryId($categoryId) {
        $result = array();
        foreach ($this->query('SELECT * FROM content WHERE valid = 1 AND category_id = ' . $categoryId . ' ORDER BY id') as $row) {
            $content = new Content();
            ContentMapper::map($content, $row);
            $result[$content->getId()] = $content;
        }
        return $result;
    }

    /**
     * Find {@link Content} by identifier.
     * @return Content Content or <i>null</i> if not found
     */
    public function findById($id) {
        $row = $this->query('SELECT * FROM content WHERE valid = 1 and id = ' . (int) $id)->fetch();
        if (!$row) {
            return null;
        }
        $content = new Content();
        ContentMapper::map($content, $row);
        return $content;
    }

    /**
     * Save {@link Content}.
     * @param Content $content {@link Content} to be saved
     * @return Content saved {@link Content} instance
     */
    public function save(Content $content) {
        if ($content->getId() === null) {
            return $this->insert($content);
        }
        return $this->update($content);
    }

    /**
     * Delete {@link Content} by identifier.
     * @param int $id {@link Content} identifier
     * @return bool <i>true</i> on success, <i>false</i> otherwise
     */
    public function delete($id) {
        $sql = '
            DELETE FROM content
            WHERE id = :id';
        $statement = $this->getDb()->prepare($sql);
        $this->executeStatement($statement, array(
            ':id' => $id,
        ));
        return $statement->rowCount() == 1;
    }

    /**
     * @return PDO
     */
    private function getDb() {
        if ($this->db !== null) {
            return $this->db;
        }
        $config = Config::getConfig("db");
        try {
            $this->db = new PDO($config['dsn'], $config['username'], $config['password']);
        } catch (Exception $ex) {
            throw new Exception('DB connection error: ' . $ex->getMessage());
        }
        return $this->db;
    }

    private function getFindSql(ContentSearchCriteria $search = null) {
        $sql = 'SELECT * FROM content WHERE deleted = 0 ';
        $orderBy = ' priority, due_on';
        if ($search !== null) {
            if ($search->getStatus() !== null) {
                $sql .= 'AND status = ' . $this->getDb()->quote($search->getStatus());
                switch ($search->getStatus()) {
                    case Content::STATUS_PENDING:
                        $orderBy = 'due_on, priority';
                        break;
                    case Content::STATUS_DONE:
                    case Content::STATUS_VOIDED:
                        $orderBy = 'due_on DESC, priority';
                        break;
                    default:
                        throw new Exception('No order for status: ' . $search->getStatus());
                }
            }
        }
        $sql .= ' ORDER BY ' . $orderBy;
        return $sql;
    }

    /**
     * @return Content
     * @throws Exception
     */
    private function insert(Content $content) {
        $now = new DateTime();
        $content->setId(null);
        $content->setDate($now);
        $sql = '
            INSERT INTO content (id, date, title, text, valid, category_id, grades_number, grades_total, user_ip, last_modified_on)
            VALUES (:id, :date, :title, :text, :valid, :category_id, :grades_number, :grades_total, :user_ip, :last_modified_on)';
        return $this->execute($sql, $content);
    }

    /**
     * @return Content
     * @throws Exception
     */
    private function update(Content $content) {
        $content->setLastModifiedOn(new DateTime());
        $sql = '
            UPDATE content
            SET
                id = :id, 
                date = :date,
                title = :title,
                text = :text,
                valid = :valid,
                category_id = :category_id,
                grades_number = :grades_number,
                grades_total = :grades_total,
                user_ip = :user_ip,
                last_modified_on = :last_modified_on
            WHERE id = :id';
        return $this->execute($sql, $content);
    }
    
    /**
     * @return Content
     * @throws Exception
     */
    private function updateValid(Content $content) {
        $content->setLastModifiedOn(new DateTime());
        $sql = '
            UPDATE content
            SET
                valid = :valid,
                last_modified_on = :last_modified_on
            WHERE id = :id';
        return $this->execute($sql, $content);
    }
    
    /**
     * @return Content
     * @throws Exception
     */
    private function updateGrade(Content $content) {
        $content->setLastModifiedOn(new DateTime());
        $sql = '
            UPDATE content
            SET
                grades_number = :grades_number,
                grades_total = :grades_total,
                last_modified_on = :last_modified_on
            WHERE id = :id';
        return $this->execute($sql, $content);
    }

    /**
     * @return Content
     * @throws Exception
     */
    private function execute($sql, Content $content) {
        $statement = $this->getDb()->prepare($sql);
        $this->executeStatement($statement, $this->getParams($content));
        if (!$content->getId()) {
            return $this->findById($this->getDb()->lastInsertId());
        }
        if (!$statement->rowCount()) {
            throw new NotFoundException('Content with ID "' . $content->getId() . '" does not exist.');
        }
        return $content;
    }

    private function getParams(Content $content) {
        $params = array(
            ':id' => $content->getId(),
            ':date' => self::formatDateTime($content->getDate()),
            ':title' => $content->getTitle(),
            ':text' => $content->getText(),
            ':valid' => $content->getValid(),
            ':category_id' => $content->getCategoryId(),
            ':grades_number' => $content->getGradesNumber(),
            ':grades_total' => $content->getGradesTotal(),
            ':user_ip' => $content->getUserIp(),
            ':last_modified_on' => $content->getLastModifiedOn(),
        );
        if ($content->getId()) {
            // unset created date, this one is never updated
            unset($params[':date']);
        }
        return $params;
    }

    private function executeStatement(PDOStatement $statement, array $params) {
        if (!$statement->execute($params)) {
            self::throwDbError($this->getDb()->errorInfo());
        }
    }

    /**
     * @return PDOStatement
     */
    private function query($sql) {
        $statement = $this->getDb()->query($sql, PDO::FETCH_ASSOC);
        if ($statement === false) {
            self::throwDbError($this->getDb()->errorInfo());
        }
        return $statement;
    }

    private static function throwDbError(array $errorInfo) {
        // Content log error, send email, etc.
        throw new Exception('DB error [' . $errorInfo[0] . ', ' . $errorInfo[1] . ']: ' . $errorInfo[2]);
    }

    private static function formatDateTime(DateTime $date) {
        return $date->format(DateTime::ISO8601);
    }

}

?>
